package com.stuManager.dao;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.stuManager.model.CommonResponseModel;
import com.stuManager.model.Student;
import com.stuManager.model.User;
import com.stuManager.util.DBUtil;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/*
    UserDao
    处理数据库管理员表的相关操作
    管理员表有两个，一个保存用户名密码，另一个保存登录token和登录时间
    增  不提供增加管理员的操作, 下面是登录后保存一条token的方法，有则更新无则插入
        int saveToken(User user, String token)
    删  无
    改  无
    查  List<JSONObject> fetchUserList ()
        User fetchUserById(int userId)
 */
public class UserDao {
    //通过userId获取一个User
    public User fetchUserById(int userId) throws SQLException {
        //1. 获取连接
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        try {
            con = DBUtil.getConnection();
            //2 准备sql语句
            String sql = "SELECT * FROM dbuser WHERE uid = ?";
            //3 创建预处理对象
            assert con != null;
            ps = con.prepareStatement(sql);
            //4 进行查询
            ps.setInt(1, userId);
            //5 获取结果集
            resultSet = ps.executeQuery();
            if (resultSet.next()) {
                int id = resultSet.getInt("uid");
                String name = resultSet.getString("username");
                String group = resultSet.getString("ugroup");
                return new User(id, name, group);
            }
            else {
                return null;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(con, ps, resultSet);
        }
        return null;
    }

    public List<JSONObject> fetchUserList () {
        //1. 获取连接
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        List<JSONObject> users = new ArrayList<>();
        try {
            con = DBUtil.getConnection();
            //2 准备sql语句, 双表查询, 使用了左外连接
            String sql = "SELECT d.`uid`, d.`username`, d.`ugroup`, t.`loginTime`, t.`lastVisitTime` " +
                    "FROM dbuser d LEFT JOIN userToken t on d.`uid` = t.`uid`";
            //3 创建预处理对象
            assert con != null;
            ps = con.prepareStatement(sql);
            //4 进行查询
            //5 获取结果集
            resultSet = ps.executeQuery();
            while (resultSet.next()) {
                JSONObject json = new JSONObject();
                json.put("id", resultSet.getInt("uid"));
                json.put("name", resultSet.getString("username"));
                json.put("group", resultSet.getString("ugroup"));
                Timestamp tokentime = resultSet.getTimestamp("loginTime");
                Timestamp lasttime = resultSet.getTimestamp("lastVisitTime");
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                json.put("tokentime", sdf.format(tokentime));
                json.put("lasttime", sdf.format(tokentime));
                users.add(json);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(con, ps, resultSet);
        }
        return users;
    }

    /*
        checkInUser
        登录操作，返回CommonResponseModel
     */
    public CommonResponseModel checkInUser(User user) throws SQLException {
        CommonResponseModel loginResponse = new CommonResponseModel();
        if (user.getUserName().length() == 0 || user.getPassword().length() == 0) {
            loginResponse.setErrorCode(1);
            loginResponse.setMessage("用户名或密码有误");
            return loginResponse;
        }
        //1. 获取连接
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        try {
            con = DBUtil.getConnection();
            //2 准备sql语句
            String sql = "SELECT * FROM dbuser WHERE userName = ? and password = ?";
            //3 创建预处理对象
            assert con != null;
            ps = con.prepareStatement(sql);
            //4 进行查询
            ps.setString(1, user.getUserName());
            ps.setString(2, user.getPassword().toUpperCase());
            //5 获取结果集
            resultSet = ps.executeQuery();
            if (resultSet.next()) {
                int id = resultSet.getInt("uid");
                String name = resultSet.getString("username");
                String password = resultSet.getString("password");
                String group = resultSet.getString("ugroup");
                User nuser = new User(id, name, group);
                loginResponse.setErrorCode(0);
                loginResponse.setMessage("登录成功");
                loginResponse.setResult(nuser);
                return loginResponse;
            }
            else {
                loginResponse.setErrorCode(2);
                loginResponse.setMessage("用户名或密码有误");
                loginResponse.setResult(null);
                return loginResponse;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(con, ps, resultSet);
        }
        loginResponse.setErrorCode(101);
        loginResponse.setMessage("发生未知错误");
        return loginResponse;
    }

    /*
        updateTokenTime
        更新某个用户的登录时间/最后访问时间
        column: 登录时间或最后访问时间 - loginTime lastVisitTime
        user:管理用户
     */
    public int updateTokenTime(User user, String column) {
        if (user.getUserName().length() == 0) {
            return 0;
        }
        //1. 获取连接
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        long timestamp = System.currentTimeMillis() / 1000;
        try {
            System.out.println("开始更改");
            con = DBUtil.getConnection();
            String sql = "UPDATE usertoken set " + column + "=FROM_UNIXTIME(?) WHERE uid=?";
            assert con != null;

            ps = con.prepareStatement(sql);
            ps.setLong(1, timestamp);
            ps.setInt(2, user.getUid());
            return ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            DBUtil.close(con, ps, resultSet);
        }
        return 0;
    }


    //存储一个新的token
    public int saveToken(User user, String token) throws SQLException {
        if (user.getUserName().length() == 0 ) {
            System.out.println("用户错误");
            return 0;
        }

        //1. 获取连接
        Connection con = null;
        PreparedStatement ps = null;
        long timestamp = System.currentTimeMillis() / 1000;
        try {
            con = DBUtil.getConnection();
            //2 准备sql语句  有则更新无则插入
            String sql = "INSERT INTO userToken (uid, token, loginTime, lastVisitTime) VALUES (?, ?, FROM_UNIXTIME(?), FROM_UNIXTIME(?)) " +
                    "ON DUPLICATE KEY UPDATE uid = ?, token = ?, loginTime = FROM_UNIXTIME(?), lastVisitTime = FROM_UNIXTIME(?)";
            //3 创建预处理对象
            assert con != null;
            ps = con.prepareStatement(sql);
            //4 设置参数
            ps.setInt(1, user.getUid());
            ps.setString(2, token);
            ps.setLong(3, timestamp);
            ps.setLong(4, timestamp);

            ps.setInt(5, user.getUid());
            ps.setString(6, token);
            ps.setLong(7, timestamp);
            ps.setLong(8, timestamp);
            //5 执行sql语句
            return ps.executeUpdate();
        }
        catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            DBUtil.closeConnection(con, ps);
        }
        return 0;
    }

    /*
        removeToken 退出登录
        从数据库和cookie移除存储的token
     */
    public int removeToken(int userid) throws SQLException {
        System.out.println("退出登录");
        //1. 获取连接
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        try {
            con = DBUtil.getConnection();
            //2 准备sql语句
            String sql = "SELECT * FROM userToken WHERE uid = ?";
            //3 创建预处理对象
            assert con != null;
            ps = con.prepareStatement(sql);
            System.out.println("查询参数 token = " + userid);
            //4. 设置参数
            ps.setInt(1, userid);
            //5 获取结果集
            resultSet = ps.executeQuery();
            if (resultSet.next()) {
                //存在这条记录
                System.out.println("准备删除");
                sql = "DELETE FROM userToken WHERE uid = ?";
                ps = con.prepareStatement(sql);
                ps.setInt(1, userid);
                int deleteRow = ps.executeUpdate();
                System.out.println("删除完毕，返回行数" + deleteRow);
                return deleteRow;
            }
            else {
                return -1;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            DBUtil.close(con, ps, resultSet);
        }
        return -1;
    }

    //查询token，如果存在，返回一个对应的user
    public User seekToken(String token) throws SQLException {
        if (token.length() == 0) {
            return null;
        }
        //1. 获取连接
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        Date date = new Date();
        java.sql.Date date1 = new java.sql.Date(date.getTime());
        try {
            con = DBUtil.getConnection();
            //2 准备sql语句
            String sql = "SELECT * FROM userToken WHERE token = ?";
            //3 创建预处理对象
            assert con != null;
            ps = con.prepareStatement(sql);
            System.out.println("查询参数 token = " + token);
            //4. 设置参数
            ps.setString(1, token);
            //5 获取结果集
            resultSet = ps.executeQuery();
            if (resultSet.next()) {
                int id = resultSet.getInt("uid");
                System.out.println("获得的结果id = " + id);
                return fetchUserById(id);
            }
            else {
                return null;
            }
        }
        catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            DBUtil.close(con, ps, resultSet);
        }
        return null;
    }
}
